set more off
clear
clear matrix
clear mata
set maxvar 32767


global CAPAPE "C:\Users\V.BERMEJO.B1\OneDrive - Universitat Ramón Llull\Escritorio\CAPAPE"
global outputtex "C:\Users\V.BERMEJO.B1\OneDrive - Universitat Ramón Llull\Escritorio\CAPAPE\tex"


use "$CAPAPE\DATA\RAW_DATA\Alphas\monthly_returns_database.dta", clear

merge n:1 month using "$CAPAPE\DATA\RAW_DATA\Alphas\ACWI_month.dta"
keep if _merge == 3
drop _merge
merge n:1 month using "$CAPAPE\DATA\RAW_DATA\Alphas\Global_3factors_monthly.dta"
keep if _merge == 3
drop _merge
merge n:1 month using "$CAPAPE\DATA\RAW_DATA\Alphas\Momentum_factor_monthly.dta"
keep if _merge == 3
drop _merge
sort month date
save "$CAPAPE\DATA\RAW_DATA\Alphas\monthly_returns_database.dta", replace

use "$CAPAPE\DATA\RAW_DATA\Alphas\monthly_returns_database.dta", clear
sort ISIN date
gen year=year(date)
replace ISIN = upper(ISIN)
merge n:1 ISIN year using "$CAPAPE\DATA\RAW_DATA\Alphas\NBIMentryexit.dta", keepusing(NBIM NBIMt_1 ManagementScore ManagementScore_n_1 ManagementScore_n_2 FTSE FTSEt_1 NBIM_holding_EqMV_USD MV TotalAssets excluded_ethics outlierSCORE NBIMportf_exit NBIMportf_entry NBIMportf_exit_after* NBIMportf_exit_before NBIMportf_entry_after* NBIMportf_entry_before NBIM_entry_vs_ALL NBIM_exit_vs_ALL)
keep if _merge == 3 // drop 2016 values
drop _merge
save "$CAPAPE\DATA\RAW_DATA\Alphas\returns_database_forportfolio.dta", replace


use "$CAPAPE\DATA\RAW_DATA\Alphas\returns_database_forportfolio.dta", clear

gen excess_ret = return_month - rf if return_month != . & ManagementScore_n_1 != . 

qui: bys ISIN: asreg excess_ret mktrf smb hml wml, wind(month 24) min(12) rmse // // te crea una beta cada mes utilizando xa la regresión los factores y retornos en los 36 meses pasados.
gen alpha4F_36m=excess_ret - _b_mktrf*mktrf - _b_smb*smb - _b_hml*hml - _b_wml*wml


*************************************************************************************
** IMPORTANT!!!! Swith conditions below to get discretionary or non-discretionray	*
*************************************************************************************

* To make it discretionary: NBIM == 1 & FTSE == 0 & outlier == 0 & outlierSCORE == 0  
*keep if NBIM == 1 & FTSE == 0 & outlier == 0 & outlierSCORE == 0  

*To make it non-discretionary: NBIM == 1 & FTSE == 1 & outlier == 0 & outlierSCORE == 0
keep if NBIM == 1 & FTSE == 1 & outlier == 0 & outlierSCORE == 0  


bysort year: egen quint_ManagScore = xtile(ManagementScore_n_1), nq(5) 

sort date quint_ManagScore 
by date quint_ManagScore: egen total_NBIM_date = total (NBIM_holding_EqMV_USD) if outlier == 0 & outlierSCORE == 0 
sort ISIN date
gen NBIM_fund_perc = (NBIM_holding_EqMV_USD / total_NBIM_date) if outlier == 0 & outlierSCORE == 0  // de 0 a 1; shows the % that the firm represents on the NBIM fund's total holdings per year. El máximo es 2% que significa que la firma representa el 2% de todos los holdings del fund.
by ISIN: gen NBIM_fund_perc_t1 = NBIM_fund_perc[_n-11]


sort date quint_ManagScore

bysort date quint_ManagScore: egen count_alpha4F_36m = count(alpha4F_36m)
gen weight_count_alpha4F_36m = (1/count_alpha4F_36m)*(1/12) 

gen double_weight= NBIM_fund_perc_t1*weight_count_alpha4F_36m

gen POST = 0 if year >2008 & year < 2012
replace POST = 1 if year > 2011 & year < 2016

keep if year > 2008

* To calculate significance in differences among top & bottom governance quantiles in pre & post periods
preserve
keep if quint_ManagScore == 1 | quint_ManagScore == 5
replace quint_ManagScore = 0 if quint_ManagScore == 1 
replace quint_ManagScore = 1 if quint_ManagScore == 5
reg alpha4F_36m quint_ManagScore if POST == 0 [aw=weight_count_alpha4F_36m], vce(cl ISIN)
reg alpha4F_36m quint_ManagScore if POST == 1 [aw=weight_count_alpha4F_36m], vce(cl ISIN)
reg alpha4F_36m quint_ManagScore if POST == 0 [aw=double_weight], vce(cl ISIN)
reg alpha4F_36m quint_ManagScore if POST == 1 [aw=double_weight], vce(cl ISIN)
restore


* Primero hago los equally weighted
preserve
collapse (mean) alpha4F_36m (semean) SE_alpha4F_36m=alpha4F_36m [aw=weight_count_alpha4F_36m], by (quint_ManagScore POST)
keep if quint_ManagScore !=. 
reshape wide alpha4F_36m SE_alpha4F_36m, i(quint_ManagScore) j(POST)
export excel using "$CAPAPE\DATA\RAW_DATA\Alphas\results_monthly_alpha4F.xlsx", sheetreplace firstrow(variables) cell(a1)


* "aw=double weight" es xa value-weighted results
restore
preserve
collapse (mean) alpha4F_36m (semean) SE_alpha4F_36m=alpha4F_36m [aw=double_weight], by (quint_ManagScore POST)
keep if quint_ManagScore !=. 
reshape wide alpha4F_36m SE_alpha4F_36m, i(quint_ManagScore) j(POST)
export excel using "$CAPAPE\DATA\RAW_DATA\Alphas\results_monthly_alpha4F.xlsx", sheetmodify firstrow(variables) cell(a8)
restore




